/*
 * Created on 05-Oct-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.traffic.si;

import java.sql.ResultSet;
import java.util.StringTokenizer;
import java.util.Vector;

import pc.sql.value.*;
import pc.system.Record;
import pc.system.Tasks;
import pc.util.Utils;
import wstrader.task.trade.TradeTask;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Instruction extends TradeTask {
	public long lsRec=0;
	private String String;
	  public Instruction(Tasks owner)
	  {
	    super(owner);
	  }
	  
	  public long doTask()
	  {
	    String table_name = "shipping_instruction";
	    int task_id     = this.getRequest().getIntValue("task_id");

	    long focused_id = this.getRequest().getLongValue("shipping_instruction");
	    switch (task_id)
		{
	      case 1 : // Update
	      	focused_id = doUpdate(table_name,focused_id);
	      	updateAccessories(focused_id);
	      	lsRec=focused_id;
	        break;
	      case 2 : // Delete
	        focused_id = doDelete(table_name,focused_id);
	        focused_id = 1;
	        break;
	      case 3: // allocate
	    	  doAllocationUpdate();
	    	  focused_id=1;
	      case 4: // allocate
	    	  doCreateWgtNoteIssue();
	    	  focused_id=1;	    	  
	      default : // View only
	        break;
	    }
	    this.setFocusedID(focused_id);	  
	    return focused_id;
	  }
	  
	  public ResultSet getContract_List()
	  {
	  	return getSQLCommand().executeQuery("select id,ref_number as name from contracts where type='S'");
	  }
	  
	  public ResultSet getBroker_List()
	  {
	  	return getSQLCommand().executeQuery("select id,contract_no as name from contracts where type='S'");
	  }	  
	  
	  public ResultSet fillShipInsListBox()
	  {
	  	int CntId = getRequest().getIntValue("contracts");
	  	String Sql ="";
	  	if (CntId == 0)
	  	{
	  		Sql ="select id,ref_number from shipping_instruction";
	  	}
	  	else
	  	{
	  		Sql= "select id,ref_number from shipping_instruction where contract_id = " + CntId;
	  	}
	  	return getSQLCommand().executeQuery(Sql);
	  }
	  
	  public ResultSet getContractDetails(int id)
	  {
	  	int CntId=0;
	  	if (id == 0)
	  	{
	  		CntId = getRequest().getIntValue("contract_id");
	  	}
	  	else
	  	{
	  		CntId = id;
	  	}
	  	String Sql = "select * from contracts details where id = " + CntId;
	  	return getSQLCommand().executeQuery(Sql);
	  }
	  
	  public ResultSet getShippingInstDetails()
	  {
	  	String sql ="";
	  	long id = getRequest().getIntValue("shipping_instruction");
	  	if (id <= 0)
	  	{
	  		id=lsRec;
	  	}
	  	
	  	int CntId = getRequest().getIntValue("contracts");
		
	  	if (id > 0)
	  	{
	  	sql = "SELECT contracts.contract_no,contracts.ref_number as cnt_ref_number,contracts.supplier_buyer_id, " +
			  "contracts.supplier_buyer_ref,contracts.quality_id,contracts.origin_id, " +
              "contracts.grade_id,contracts.packing_id,contracts.tons as cnttons, " +
			  "shipping_instruction.*,shipping_instruction.tons as tons " +
			  "FROM shipping_instruction " +
			  "INNER JOIN contracts ON (shipping_instruction.contract_id=contracts.id) " +
			  "WHERE shipping_instruction.id = " + id;
	  	}
	  	else
	  	{
	  	sql = "select 0 as shipper_id,null as invoice_no,null as invoice_date,id as contract_id,ref_number as cnt_ref_number,contract_no, " +
		      "origin_id, quality_id, grade_id ,warehouse_id, tons as cnttons, " +
		      "supplier_buyer_id,supplier_buyer_ref,packing_id,pending_tons as tons, " +
			  "kg_per_bag, (pending_tons*1000)/kg_per_bag as no_of_bags from contracts " +
			  "where id = " + CntId;
	  	}
	  	return getSQLCommand().executeQuery(sql);
	  }
	  
	  public ResultSet getPort_List(String country_code)
	  {
	    String sql = "SELECT * FROM port_master WHERE country_code = ? OR ? ";
	    Vector vp = new Vector();
	    vp.addElement(new StringValue(country_code));
	    vp.addElement(new BooleanValue(country_code == null));
	    return getSQLCommand().executeQuery(sql,vp);
	  }
	  
	  public ResultSet get_Accessory_Detail()
	  {
	  	int id = getRequest().getIntValue("shipping_instruction");
	    String sql =
	        "SELECT IFNULL(ac.id,0)           AS id," +
	        "       am.id                     AS accessory_id," +
	        "       IFNULL(ac.quantity,0)     AS quantity," +
	        "       IFNULL(ac.supplier_id,0)  AS supplier_id," +
	        "       am.* " +
	        "FROM accessory_master am " +
	        "LEFT JOIN accessories ac ON am.id = ac.accessory_id AND ac.inst_id = " + id + " " +
	        "ORDER BY am.id";
	    //getSQLCommand().setSort(true);
	    return getSQLCommand().executeQuery(sql);
	  }	  
	  
	  public ResultSet getDocument_List()
	  {
	  	int id = getRequest().getIntValue("shipping_instruction");
	    String sql =
	        "SELECT IFNULL(do.selected,0) AS selected, " +
	        "       IFNULL(do.id,0) AS id, " +
	        "       dm.id AS document_id," +
	        "       dm.name " +
	        "FROM document_master dm " +
	        "LEFT JOIN documents do ON dm.id = do.document_id AND do.inst_id = " + id + " " +
	        "WHERE dm.active = 1 AND dm.type = 'S' " +
	        "ORDER BY dm.no";
	    return getSQLCommand().executeQuery(sql);
	  }	 
	  
	  public ResultSet getContainer_Detail()
	  {
	  	int id = getRequest().getIntValue("shipping_instruction");
	  	System.out.println("Request Called From JSP");
	    String sql =
	        "SELECT wn.* FROM weight_note_ex wn " +
	        "WHERE wn.inst_id = " + id + " AND wn.type = 'E' ";
	    System.out.println(sql);
	    return getSQLCommand().executeQuery(sql);
	  }	  
	  
	  public ResultSet getSI_ListView()
	  {
	  	String sql = "";
	  	/* sql = "SELECT shipping_instruction.*,sum(weight_note_ex.net_weight) as shipton,sum(weight_note_ex.no_of_bags) as shipbag, " +
		"grade_master.name,grade_master.origin_id,grade_master.quality_id,grade_master.grade_code  " +
		"FROM shipping_instruction " +
		"INNER JOIN weight_note_ex ON (shipping_instruction.id=weight_note_ex.inst_id) " +
		"INNER JOIN grade_master ON (shipping_instruction.grade_id=grade_master.id) " +
		"WHERE weight_note_ex.type = 'E' GROUP BY `weight_note_ex`.`inst_id`";*/

	  	sql = "SELECT shipping_instruction.*,0 as shipton,0 as shipbag, " +
	  			"grade_master.name,grade_master.origin_id,grade_master.quality_id,grade_master.grade_code  " +
	  			"FROM shipping_instruction " +
	  			"INNER JOIN grade_master ON (shipping_instruction.grade_id=grade_master.id)";
	  	
	  	return getSQLCommand().executeQuery(sql);
	  }	  
	  
	  public String getNew_SI()
	  {
		int dy = Utils.getCurYear() % 100;
		String yrs = Utils.formatNumber(dy,2);
	  	String No="";
	    String sql =
	        "SELECT MAX(ref_number) as ref_number FROM shipping_instruction ";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
	    
	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "SWCVN-DI-"+yrs+"-00000";
	    String sub = "";
	    rc.close();
	    
	    if (ref=="") ref="SWCVN-SI-"+yrs+"-00000";
	    int RefNo=Utils.strToInt(ref.substring(12,17))+1;
	    
	    if (Utils.numberToStr(RefNo,0,false).length()==5) ref = Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==4) ref = "0" + Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==3) ref = "00" + Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==2) ref = "000" + Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==1) ref = "0000" + Utils.numberToStr(RefNo,0,false);
	    
	    No = "SWCVN-SI-"+yrs + "-" + ref;//"WS-SI" + " " + this.getNextRefNumber(ref);
	    return No;
	  }
	  
	  public String getNew_SIRef()
	  {
	    String sql = "SELECT MAX(ref_number) as ref_number FROM shipping_instruction";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "SWCVN-SI 00-00000";
	    rc.close();
	    return "SWCVN-SI" + " " + this.getNextRefNumber(ref);
	  }
	  
	  public ResultSet getContract_Allocated()
	  {
	  	long Sid = getRequest().getIntValue("shipping_instruction");
	    String sql =
	        "SELECT wn_id,weight_out " +
	        "FROM wnr_allocation " +
	        "WHERE (inst_type = 'S') " +
	        "  AND (inst_id = ?)";

	    Vector vp = new Vector();
	    vp.addElement(new LongValue(Sid));
	    return getSQLCommand().executeQuery(sql,vp);
	  }	  

	  public long updateAccessories(long ShipInsId)
	  {
		  int rowcount =getRequest().getIntValue("acc_rows");
		  int recid =0;
		  int accid = 0;
		  float quantity =0;
		  int supplierid =0;
		  int count =1;
		  String SqlStr="";
		  long RecExist = this.findID("accessories","inst_id",ShipInsId);

		  while (count <= rowcount)
		  {
			 recid = getRequest().getIntValue("ac_id_"+count);
			 accid= getRequest().getIntValue("accessory_id_"+count);
			 quantity = getRequest().getFloatValue("quantity_"+count);
			 supplierid = getRequest().getIntValue("supplier_id_"+count);
			 
		      	if (RecExist > 0)
		      	{
		      		SqlStr = "update accessories " + 
		      				"SET inst_id = ?, " +
		      				"accessory_id = ?, " +
		      				"quantity = ?, " +
		      				"supplier_id = ? " +
		      				"where id = ?";
		      		Vector vp = new Vector();
		      		vp.addElement(new LongValue(ShipInsId));
		      		vp.addElement(new IntValue(accid));
		      		vp.addElement(new FloatValue(quantity));
		      		vp.addElement(new IntValue(supplierid));
		      		vp.addElement(new IntValue(recid));
		      		int ret = getSQLCommand().executeUpdate(SqlStr,vp);	
		      	}
		      	else
		      	{
		      		SqlStr = "insert into accessories " +
		      				"SET inst_id = ?, " +
		      				"accessory_id = ?, " +
		      				"quantity = ?, " +
		      				"supplier_id = ?";
		      		Vector vp = new Vector();
		      		vp.addElement(new LongValue(ShipInsId));
		      		vp.addElement(new IntValue(accid));
		      		vp.addElement(new FloatValue(quantity));
		      		vp.addElement(new IntValue(supplierid));
		      		int ret = getSQLCommand().executeUpdate(SqlStr,vp);			      		
		      	}
			 
			 count++;
		  }
		  count =1;

		  return ShipInsId;
	  }	  

	  public ResultSet fetchPurCntUnAlcList(String Grade)
	  {
	  	int Gid = Utils.strToInt(Grade);
	  	String Sql = "Select id,ref_number,grade_id,net_weight as tons,(net_weight-allocated_weight) as avltons from weight_note where (net_weight-allocated_weight) > 1 and grade_id = "+Gid;
	  	
	  	return getSQLCommand().executeQuery(Sql);
	  }
	  
	  public float doAllocationUpdate()
	  {
		  float Ret=0;
		  String SelId = getRequest().getStringValue("sel_id");
		  int ContractId = getRequest().getIntValue("contracts");
		  int Sid = getRequest().getIntValue("shipping_instruction");
		  StringTokenizer st = new StringTokenizer(SelId, ",");
		  int count = st.countTokens();
		  int loop =1;
		  int id =0;
		  float tons=0;
		  String groupstr = "";
		  while (loop <= count)
		  {
			groupstr = st.nextToken();
			StringTokenizer val = new StringTokenizer(groupstr, "/");
			tons = Utils.strToFloat(val.nextToken());
			id = Utils.strToInt(val.nextToken());
			
			String SqlStr = "insert into contract_allocation " +
      				"SET contract_id = ?, " +
      				"inst_id = ?, " +
      				"inst_type = ?, " +
      				"allocated_tons = ?, " +
      				"allocated_bags = ?, " +
      				"date = ?, " + 
      				"time = ?, " +
      				"user_id =?";
      		Vector vp = new Vector();
      		vp.addElement(new IntValue(ContractId));
      		vp.addElement(new IntValue(Sid));
      		vp.addElement(new StringValue("S"));
      		vp.addElement(new FloatValue(tons));
      		vp.addElement(new FloatValue(0));
      		vp.addElement(new DateValue(Utils.today()));
      		vp.addElement(new TimeValue(Utils.now()));
      		vp.addElement(new IntValue(this.getUser().getUserID()));
      		int ret = getSQLCommand().executeUpdate(SqlStr,vp);		
			
      		SqlStr = "insert into wnr_allocation " +
      				"SET inst_id = ?, " +
      				"inst_type = ?, " +
      				"wn_id = ?, " +
      				"weight_out = ?, " +
      				"date = ?, " + 
      				"time = ?";
      		Vector vpWNAlc = new Vector();
      		vpWNAlc.addElement(new IntValue(Sid));
      		vpWNAlc.addElement(new StringValue("S"));
      		vpWNAlc.addElement(new IntValue(id));
      		vpWNAlc.addElement(new FloatValue(tons));
      		vpWNAlc.addElement(new DateValue(Utils.today()));
      		vpWNAlc.addElement(new TimeValue(Utils.now()));
      		ret = getSQLCommand().executeUpdate(SqlStr,vpWNAlc);	
      		
      		float WN_AlcTons = this.getSum("weight_out", "wn_id", "wnr_allocation", id);
      		getSQLCommand().executeUpdate("Update weight_note set allocated_weight = "+ WN_AlcTons + "where id = "+ id);
			loop++;  
		  }
		  
		  return Ret;
	  }

	  public float doCreateWgtNoteIssue()	 
	  {
		  String WNI_RefNo = getRequest().getStringValue("ref_number_0");
		  String ContainerNo = getRequest().getStringValue("container_no_0");
		  String SealNo = getRequest().getStringValue("seal_no_0");
		  float WnNetWgt = getRequest().getFloatValue("wn_net_weight_0");
		  float WnNoOfBags = getRequest().getFloatValue("wn_no_of_bags_0");
		  float WnTareWgt = getRequest().getFloatValue("wn_tare_weight_0");
		  float WnGrossWgt = getRequest().getFloatValue("wn_gross_weight_0");
		  int Sid = getRequest().getIntValue("shipping_instruction");
		  int Gid = getRequest().getIntValue("grade_id");
		  
		  String SqlStr = "insert into weight_note_ex " +
    				"SET ref_number = ?, " +
    				"type = ?, " +
    				"date = ?, " +
    				"time = ?, " +
    				"inst_id = ?, " +
    				"grade_id = ?, " + 
    				"container_no = ?, " +
    				"seal_no = ?, " +
    				"gross_weight = ?, "+
    				"tare_weight = ?, " +
    				"net_weight = ?, " +
    				"no_of_bags = ?, " +
    				"user_id = ?";
    		Vector vp = new Vector();
    		vp.addElement(new StringValue(WNI_RefNo));
    		vp.addElement(new StringValue("E"));
    		vp.addElement(new DateValue(Utils.today()));
    		vp.addElement(new TimeValue(Utils.now()));
    		vp.addElement(new IntValue(Sid));
    		vp.addElement(new IntValue(Gid));
    		vp.addElement(new StringValue(ContainerNo));
    		vp.addElement(new StringValue(SealNo));
    		vp.addElement(new FloatValue(WnGrossWgt));
    		vp.addElement(new FloatValue(WnTareWgt));
    		vp.addElement(new FloatValue(WnNetWgt));
    		vp.addElement(new FloatValue(WnNoOfBags));
    		vp.addElement(new IntValue(this.getUser().getUserID()));
    		int ret = getSQLCommand().executeUpdate(SqlStr,vp);			  
		  return ret;
	  }
	  
}
